const { PostModel, UserModel } = require('../../models/index')
const { QueryTypes } = require('sequelize')
const sequelize = require('../../config/database')

// 发布攻略
const publishPost = async (req, res) => {
  try {
    // 获取得到的攻略数据
    const postData = req.body

    if (!postData) {
      return res.status(400).json({
        error: '攻略数据不能为空'
      })
    }

    // 发布攻略
    const publishData = await PostModel.create(postData)

    // 返回创建成功的响应
    res.status(200).json({
      title: '攻略创建成功',
      data: publishData.post_id
    })

  } catch (error) {
    res.status(500).json({
      error: error.message
    })
  }
}

// 获取攻略数据
const getPostData = async (req, res) => {
  try {
    let { filter_type, is_hot, city_id } = req.query

    // 从分页中间件中获取分页信息
    const { offset, pageSize } = req.pagination;

    // 构建SQL基础查询语句
    let commonSubQuery = `
	SELECT
		p.post_id,
		p.title,
    p.content,
		p.images,
    p.type,
    p.is_hot,
		p.createdAt,
		u.username,
		u.avatar,
	CASE
			p.type
			WHEN 'scenery' THEN
			s.city_id
			WHEN 'hotel' THEN
			h.city_id
			WHEN 'restaurant' THEN
			r.city_id ELSE NULL
		END AS city_id
	FROM
		post p
		LEFT JOIN scenery s ON p.association_id = s.scenery_id
		AND p.type = 'scenery'
		LEFT JOIN hotel h ON p.association_id = h.hotel_id
		AND p.type = 'hotel'
		LEFT JOIN restaurant r ON p.association_id = r.restaurant_id
		AND p.type = 'restaurant'
		LEFT JOIN user u ON p.user_id = u.user_id
    `

    // 查询数据
    let sqlQuery = ''

    // 查询数据总数
    let sqlCount = ''

    // 初始化查询对象
    let sqlWhereConditions = ''
    let sqlParams = { pageSize: pageSize, offset: offset }

    // 检查并且构建查询条件
    if (filter_type) {
      sqlWhereConditions += `sub.type=:filterType AND `
      sqlParams.filterType = filter_type
    }

    if (is_hot) {
      sqlWhereConditions += `sub.is_hot=:isHot AND `
      sqlParams.isHot = is_hot
    }

    if (city_id) {
      sqlWhereConditions += `sub.city_id=:cityId AND `
      sqlParams.cityId = city_id
    }


    if (sqlWhereConditions) {
      // 移除最后一个AND
      sqlWhereConditions = sqlWhereConditions.slice(0, -5)

      // 拼接SQL查询语句
      sqlQuery = `SELECT * FROM (${commonSubQuery}) AS sub WHERE ${sqlWhereConditions} LIMIT :pageSize OFFSET :offset;`
      sqlCount = `SELECT COUNT(*) AS count FROM (${commonSubQuery}) AS sub WHERE ${sqlWhereConditions}`
    } else {
      sqlQuery = `SELECT * FROM (${commonSubQuery}) AS sub LIMIT :pageSize OFFSET :offset;`
      sqlCount = `SELECT COUNT(*) AS count FROM (${commonSubQuery}) AS sub`
    }

    const queryData = await sequelize.query(sqlQuery, { type: QueryTypes.SELECT, replacements: sqlParams })
    const queryCount = await sequelize.query(sqlCount, { type: QueryTypes.SELECT, replacements: sqlParams })
    // 将数据转化为前端需要的格式
    const formQueryData = queryData.map(item => {
      return {
        id: item.post_id,
        title: item.title,
        content: item.content,
        image: item.images.slice(0, 3)[0].url,
        type: item.type,
        createdAt: item.createdAt,
        user: {
          username: item.username,
          avatar: item.avatar
        }
      }
    })

    if (queryData.length === 0) {
      return res.status(200).json({
        title: '未找到数据',
        data: [],
        message: '没有找到符合要求的攻略数据'
      })
    } else {
      return res.status(200).json({
        title: '攻略数据',
        data: formQueryData,
        count: queryCount[0].count
      })
    }
  } catch (error) {
    res.status(500).json({
      error: error.message
    })
  }
}

// 获取社区攻略数据
const getCommunityPostData = async (req, res) => {
  try {
    let { filter_type, is_hot, city_id } = req.query

    // 从分页中间件中获取分页信息
    const { offset, pageSize } = req.pagination;

    // 构建SQL基础查询语句
    let commonSubQuery = `
	SELECT
		p.post_id,
		p.title,
    p.content,
		p.images,
    p.type,
    p.is_hot,
		p.createdAt,
		u.username,
		u.avatar,
	CASE
			p.type
			WHEN 'scenery' THEN
			s.city_id
			WHEN 'hotel' THEN
			h.city_id
			WHEN 'restaurant' THEN
			r.city_id ELSE NULL
		END AS city_id
	FROM
		post p
		LEFT JOIN scenery s ON p.association_id = s.scenery_id
		AND p.type = 'scenery'
		LEFT JOIN hotel h ON p.association_id = h.hotel_id
		AND p.type = 'hotel'
		LEFT JOIN restaurant r ON p.association_id = r.restaurant_id
		AND p.type = 'restaurant'
		LEFT JOIN user u ON p.user_id = u.user_id
    `

    // 查询数据
    let sqlQuery = ''

    // 查询数据总数
    let sqlCount = ''

    // 初始化查询对象
    let sqlWhereConditions = ''
    let sqlParams = { pageSize: pageSize, offset: offset }

    // 检查并且构建查询条件
    if (filter_type) {
      sqlWhereConditions += `sub.type=:filterType AND `
      sqlParams.filterType = filter_type
    }

    if (is_hot) {
      sqlWhereConditions += `sub.is_hot=:isHot AND `
      sqlParams.isHot = is_hot
    }

    if (city_id) {
      sqlWhereConditions += `sub.city_id=:cityId AND `
      sqlParams.cityId = city_id
    }


    if (sqlWhereConditions) {
      // 移除最后一个AND
      sqlWhereConditions = sqlWhereConditions.slice(0, -5)

      // 拼接SQL查询语句
      sqlQuery = `SELECT * FROM (${commonSubQuery}) AS sub WHERE ${sqlWhereConditions} LIMIT :pageSize OFFSET :offset;`
      sqlCount = `SELECT COUNT(*) AS count FROM (${commonSubQuery}) AS sub WHERE ${sqlWhereConditions}`
    } else {
      sqlQuery = `SELECT * FROM (${commonSubQuery}) AS sub LIMIT :pageSize OFFSET :offset;`
      sqlCount = `SELECT COUNT(*) AS count FROM (${commonSubQuery}) AS sub`
    }

    const queryData = await sequelize.query(sqlQuery, { type: QueryTypes.SELECT, replacements: sqlParams })
    const queryCount = await sequelize.query(sqlCount, { type: QueryTypes.SELECT, replacements: sqlParams })
    // 将数据转化为前端需要的格式
    const formQueryData = queryData.map(item => {
      return {
        id: item.post_id,
        title: item.title,
        content: item.content,
        image: item.images.slice(0, 3),
        type: item.type,
        createdAt: item.createdAt,
        user: {
          username: item.username,
          avatar: item.avatar
        }
      }
    })

    if (queryData.length === 0) {
      return res.status(200).json({
        title: '未找到数据',
        data: [],
        message: '没有找到符合要求的攻略数据'
      })
    } else {
      return res.status(200).json({
        title: '攻略数据',
        data: formQueryData,
        count: queryCount[0].count
      })
    }
  } catch (error) {
    res.status(500).json({
      error: error.message
    })
  }
}

// 获取攻略详情数据
const getPostDetailData = async (req, res) => {
  try {
    // 获取攻略ID
    const postID = req.params.id

    // 攻略ID为空的情况
    if (!postID) {
      return res.status(400).json({
        error: '攻略ID不能为空'
      })
    }

    // 构建SQL查询语句
    const sqlQuery = `
SELECT
	*
FROM
	(
	SELECT
		p.*,
		u.username,
		u.avatar,
	CASE
			p.type
			WHEN 'scenery' THEN
			s.name
			WHEN 'hotel' THEN
			h.name
			WHEN 'restaurant' THEN
			r.name
		END AS name,
	CASE
			p.type
			WHEN 'scenery' THEN
			s.rating
			WHEN 'hotel' THEN
			h.rating
			WHEN 'restaurant' THEN
			r.rating
		END AS rating,
	CASE
			p.type
			WHEN 'scenery' THEN
			s.images
			WHEN 'hotel' THEN
			h.images
			WHEN 'restaurant' THEN
			r.images
		END AS image
	FROM
		post p
		LEFT JOIN scenery s ON p.association_id = s.scenery_id
		AND p.type = 'scenery'
		LEFT JOIN hotel h ON p.association_id = h.hotel_id
		AND p.type = 'hotel'
		LEFT JOIN restaurant r ON p.association_id = r.restaurant_id
		AND p.type = 'restaurant'
		LEFT JOIN user u ON p.user_id = u.user_id
	) sub
WHERE
	sub.post_id = :id;
    `

    const queryData = await sequelize.query(sqlQuery, { type: QueryTypes.SELECT, replacements: { id: postID } })

    // 未查询到攻略信息的情况
    if (queryData.length === 0) {
      return res.status(404).json({
        error: '未找到攻略信息'
      })
    }

    // 将数据转化为前端期望的格式
    const formData = queryData.map(item => {
      return {
        id: item.post_id,
        title: item.title,
        content: item.content,
        images: item.images,
        createdAt: item.createdAt,
        is_hot: item.is_hot,
        user: {
          username: item.username,
          avatar: item.avatar
        },
        association: {
          type: item.type,
          detail_id: item.association_id,
          name: item.name,
          image: item.image[0].url,
          rating: item.rating
        }
      }
    })

    res.status(200).json({
      title: '攻略详情信息',
      data: formData[0]
    })
  } catch (error) {
    res.status(500).json({
      error: error.message
    })
  }
}

// 获取热门攻略数据
const getHotPostData = async (req, res) => {
  try {
    const hotPost = await PostModel.findAll({
      where: {
        is_hot: 1
      },
      attributes: ['post_id', 'title', 'images'],
      limit: 5
    })

    if (hotPost.length === 0) {
      return res.status(404).json({
        error: '未找到热门攻略'
      })
    }

    // 返回前端需要的数据格式
    const formData = hotPost.map(item => {
      return {
        id: item.post_id,
        name: item.title,
        image: item.images[0].url
      }
    })

    res.status(200).json({
      title: '热门攻略',
      data: formData
    })
  } catch (error) {
    res.status(500).json({
      error: error.message
    })
  }
}

// 获取用户个人发布的攻略数据
const getUserPostData = async (req, res) => {
  try {
    // 获取用户ID
    const userID = req.params.id

    // 用户ID为空的情况
    if (!userID) {
      return res.status(400).json({
        error: '用户ID不能为空'
      })
    }

    // 查询用户发布的攻略信息
    const queryPostData = await PostModel.findAll({
      where: {
        user_id: userID
      },
      include: [
        {
          model: UserModel,
          as: 'post_belongsUser'
        }
      ],
      order: [
        ['updatedAt', 'DESC'],
        ['createdAt', 'DESC']
      ],
    })

    // 未查询到攻略信息的情况
    if (queryPostData.length === 0) {
      return res.status(200).json({
        title: '您尚未发布攻略'
      })
    }

    // 将数据转化为前端期望的格式
    const formData = queryPostData.map(item => {
      return {
        id: item.post_id,
        title: item.title,
        image: item.images[0].url,
        createdAt: item.createdAt,
        type: item.type,
        user: {
          username: item.post_belongsUser.username,
          avatar: item.post_belongsUser.avatar
        }
      }
    })

    res.status(200).json({
      title: '用户个人发布的攻略数据',
      data: formData
    })
  } catch (error) {
    res.status(500).json({
      error: error.message
    })
  }
}

module.exports = {
  publishPost,
  getPostData,
  getCommunityPostData,
  getPostDetailData,
  getHotPostData,
  getUserPostData
}
